﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParseId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ParseId]
GO

CREATE FUNCTION [dbo].[ParseId](@Id nvarchar(MAX))
RETURNS bigint
AS
BEGIN
	IF SUBSTRING(@Id, 1, 1) = '#'
		RETURN CAST(SUBSTRING(@Id, 2, LEN(@Id) - 1) AS bigint)
	
	RETURN NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HasPermission]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HasPermission]
GO

CREATE FUNCTION dbo.HasPermission
(
	@UserRoles TNVarCharSet READONLY,
	@Id bigint,
	@Permission int
)
RETURNS int
AS
BEGIN
	IF 
		EXISTS(SELECT Value FROM @UserRoles WHERE Value = 'Administrator') OR
		EXISTS(
			SELECT DirectoryEntryId 
			FROM AccessControlList 
			WHERE 
				DirectoryEntryId = @Id AND
				[Role] = 'All' AND 
				[Permission] & @Permission = @Permission
		) OR
		EXISTS(
			SELECT DirectoryEntryId
			FROM 
				AccessControlList acl 
				INNER JOIN @UserRoles [role]
					ON 
						acl.[Role] = [role].Value OR 
						acl.[Role] LIKE [role].Value + '/%' OR
						[role].Value LIKE acl.[Role] + '/%'
			WHERE 
				acl.DirectoryEntryId = @Id AND acl.[Permission] & @Permission = @Permission
		)
		RETURN 1
	
	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPathById]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetPathById]
GO

CREATE FUNCTION [dbo].[GetPathById](@UserRoles TNVarCharSet READONLY, @Id bigint)
RETURNS nvarchar(MAX)
AS
BEGIN
	DECLARE @Path nvarchar(MAX)
	DECLARE @ParentId bigint
	
	SELECT @Path = Name, @ParentId = ParentId
	FROM DirectoryEntry
	WHERE 
		Id = @Id AND dbo.HasPermission(@UserRoles, @Id, 1) = 1
	
	IF @ParentId IS NULL AND NOT @Path IS NULL RETURN '/'
	
	WHILE NOT @ParentId IS NULL
	BEGIN
		SET @Id = @ParentId
		
		SELECT @Path = Name + '/' + @Path, @ParentId = ParentId
		FROM DirectoryEntry
		WHERE Id = @Id
	END
	
	RETURN @Path
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDirectoryEntryId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetDirectoryEntryId]
GO

CREATE PROCEDURE [dbo].[GetDirectoryEntryId] 
	@PathOrId nvarchar(MAX), 
	@ActionIfMissing int,
	@Id bigint OUTPUT
AS
BEGIN
	SET @Id = dbo.ParseId(@PathOrId)

	IF NOT @Id IS NULL RETURN 0

	IF LEN(@PathOrId) = 0 RETURN 1

	DECLARE @Head nvarchar(MAX) = NULL
	DECLARE @Tail nvarchar(MAX) = @PathOrId
	DECLARE @SlashIdx int = CHARINDEX('/', @Tail)
	DECLARE @NextId bigint

	WHILE 1 = 1
	BEGIN
		IF @SlashIdx > 0
		BEGIN
			SET @Head = SUBSTRING(@Tail, 1, @SlashIdx - 1)
			SET @Tail = SUBSTRING(@Tail, @SlashIdx + 1, LEN(@Tail) - @SlashIdx)
			SET @SlashIdx = CHARINDEX('/', @Tail)
		END
		ELSE
		BEGIN
			SET @Head = @Tail
			SET @Tail = NULL
		END
		
		SET @NextId = NULL
		
		IF @Head = ''
			SELECT @NextId = MIN(Id) FROM DirectoryEntry
		ELSE
		BEGIN
			SELECT @NextId = Id
			FROM DirectoryEntry
			WHERE ParentId = @Id AND Name = @Head			
		END
			
		IF @NextId IS NULL 
		BEGIN
			IF @ActionIfMissing = 2
			BEGIN
				INSERT INTO DirectoryEntry(Name, ParentId, CreatedOn, UpdatedOn, [Type])
				VALUES(@Head, @Id, GETUTCDATE(), GETUTCDATE(), 2)

				SET @NextId = @@IDENTITY

				INSERT INTO AccessControlList(DirectoryEntryId, [Role], [Permission])
				SELECT 
					@NextId, 
					[Role], 
					CASE 
						WHEN [Permission] & 4 <> 0 THEN [Permission] | 2 
						ELSE [Permission] 
					END
				FROM AccessControlList
				WHERE DirectoryEntryId = @Id
								
				IF @Tail IS NULL BREAK
			END
			ELSE
				BREAK
		END
		ELSE IF @Tail IS NULL
			BREAK
		
		SET @Id = @NextId
	END

	IF NOT @NextId IS NULL OR @ActionIfMissing <> 1
		SET @Id = @NextId
		
	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetInfo]
GO

CREATE PROCEDURE [dbo].[GetInfo]
	@UserRoles TNVarCharSet READONLY,
	@PathOrId nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Id bigint
	
	EXEC GetDirectoryEntryId @PathOrId, 0, @Id = @Id OUTPUT
	
	IF @Id IS NULL RETURN 1
	
	SELECT @Id AS [Id], ParentId, Name, CreatedOn, UpdatedOn, [Type]
	FROM DirectoryEntry
	WHERE Id = @Id AND dbo.HasPermission(@UserRoles, @Id, 1) = 1
	
	RETURN 0	
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRoleUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetRoleUsers]
GO

CREATE PROCEDURE [dbo].[GetRoleUsers]
	@Id bigint
AS
BEGIN
	SET NOCOUNT ON

	SELECT UserName
	FROM RoleUser
	WHERE DirectoryEntryId = @Id
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRolesUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetRolesUsers]
GO

CREATE PROCEDURE [dbo].[GetRolesUsers]
	@Ids TBigIntSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	SELECT DirectoryEntryId, UserName
	FROM RoleUser
	WHERE DirectoryEntryId IN (SELECT Value FROM @Ids)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAccessControlList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAccessControlList]
GO

CREATE PROCEDURE [dbo].[GetAccessControlList]
	@Id bigint
AS
BEGIN
	SET NOCOUNT ON

	SELECT [Role], [Permission]
	FROM AccessControlList
	WHERE DirectoryEntryId = @Id
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAccessControlLists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAccessControlLists]
GO

CREATE PROCEDURE [dbo].[GetAccessControlLists]
	@Ids TBigIntSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	SELECT DirectoryEntryId, [Role], [Permission]
	FROM AccessControlList
	WHERE DirectoryEntryId IN (SELECT Value FROM @Ids)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWorkflowPreferredProcessors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWorkflowPreferredProcessors]
GO

CREATE PROCEDURE [dbo].[GetWorkflowPreferredProcessors]
	@Id bigint
AS
BEGIN
	SET NOCOUNT ON

	SELECT ProcessorName
	FROM WorkflowPreferredProcessor
	WHERE DirectoryEntryId = @Id
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWorkflowsPreferredProcessors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWorkflowsPreferredProcessors]
GO

CREATE PROCEDURE [dbo].[GetWorkflowsPreferredProcessors]
	@Ids TBigIntSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	SELECT DirectoryEntryId, ProcessorName
	FROM WorkflowPreferredProcessor
	WHERE DirectoryEntryId IN (SELECT Value FROM @Ids)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MoveDirectoryEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MoveDirectoryEntry]
GO

CREATE PROCEDURE [dbo].[MoveDirectoryEntry]
	@UserRoles TNVarCharSet READONLY,
	@What nvarchar(MAX),
	@Where nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @WhatId bigint 
	
	EXEC GetDirectoryEntryId @What, 0, @Id = @WhatId OUTPUT
	
	IF @WhatId IS NULL RETURN 1

	IF dbo.HasPermission(@UserRoles, @WhatId, 2) = 0 RETURN 2

	DECLARE @WhereId bigint 
	
	EXEC GetDirectoryEntryId @Where, 2, @Id = @WhereId OUTPUT

	IF dbo.HasPermission(@UserRoles, @WhereId, 1 | 4) = 0 RETURN 2

	IF EXISTS(
		SELECT Id 
		FROM DirectoryEntry 
		WHERE 
			ParentId = @WhereId AND Name = (SELECT Name FROM DirectoryEntry WHERE Id = @WhatId)
	) RETURN 3

	EXEC dbo.CutEmptySubsets @DeletedId = @WhatId

	UPDATE DirectoryEntry
	SET ParentId = @WhereId
	WHERE Id = @WhatId	

	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountChildren]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CountChildren]
GO

CREATE PROCEDURE [dbo].[CountChildren]
	@UserRoles TNVarCharSet READONLY,
	@Parent nvarchar(MAX),
	@Type int
AS
BEGIN
	DECLARE @ParentId bigint
	
	EXEC GetDirectoryEntryId @Parent, 0, @Id = @ParentId OUTPUT

	IF @ParentId IS NULL OR dbo.HasPermission(@UserRoles, @ParentId, 1) = 0
		SELECT CAST(0 AS bigint)
	
	SELECT CAST(COUNT(*) AS bigint)
	FROM DirectoryEntry
	WHERE ParentId = @ParentId AND dbo.HasPermission(@UserRoles, Id, 1) = 1 AND [Type] & @Type <> 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DirectoryEntryExists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DirectoryEntryExists]
GO

CREATE PROCEDURE [dbo].[DirectoryEntryExists]
	@UserRoles TNVarCharSet READONLY,
	@PathOrId nvarchar(MAX),
	@Timestamp datetime
AS
BEGIN
	DECLARE @Id bigint
	
	EXEC GetDirectoryEntryId @PathOrId, 0, @Id = @Id OUTPUT

	IF @Id IS NULL OR dbo.HasPermission(@UserRoles, @Id, 1) = 0
	BEGIN
		SELECT 0
		RETURN 0
	END
	ELSE IF @Timestamp IS NULL
	BEGIN
		SELECT 1
		RETURN 0
	END
		
	DECLARE @UpdatedOn datetime
	
	SELECT @UpdatedOn = UpdatedOn
	FROM DirectoryEntry
	WHERE Id = @Id
	
	IF NOT @UpdatedOn IS NULL AND @UpdatedOn > @Timestamp
		SELECT 1
	ELSE
		SELECT 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CutEmptySubsets]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CutEmptySubsets]
GO

CREATE PROCEDURE [dbo].[CutEmptySubsets]
	@DeletedId bigint
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SubsetId bigint = @DeletedId
	DECLARE @ParentSubsetId bigint
	DECLARE @ParentType int
	DECLARE @SubsetsToDelete TBigIntSet
	DECLARE @Count int
	
	WHILE 1 = 1
	BEGIN
		SELECT @ParentSubsetId = ParentId
		FROM DirectoryEntry
		WHERE Id = @SubsetId	
		
		IF @ParentSubsetId IS NULL RETURN 0
		
		SELECT @ParentType = [Type]
		FROM DirectoryEntry
		WHERE Id = @ParentSubsetId	
	
		--If the parent is a set.
		IF @ParentType = 256 
			BREAK 
		--If the parent is not a folder.
		ELSE IF @ParentType <> 2
			RETURN 0
		
		SELECT @Count = COUNT(*)
		FROM DirectoryEntry
		WHERE 
			ParentId = @ParentSubsetId
			AND NOT Id IN (SELECT Value FROM @SubsetsToDelete)
			AND Id <> @DeletedId
		
		IF @Count = 0
			INSERT INTO @SubsetsToDelete(Value)
			VALUES (@ParentSubsetId)
		
		SET @SubsetId = @ParentSubsetId
	END
	
	DELETE FROM AccessControlList
	WHERE DirectoryEntryId IN (SELECT Value FROM @SubsetsToDelete)

	DELETE FROM DirectoryEntry
	WHERE Id IN (SELECT Value FROM @SubsetsToDelete)
	
	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveAccessControlList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SaveAccessControlList]
GO

CREATE PROCEDURE dbo.SaveAccessControlList
	@DirectoryEntryId bigint,
	@AccessControlList TAccessControlList READONLY
AS
BEGIN
	SET NOCOUNT ON

	DELETE FROM AccessControlList
	WHERE DirectoryEntryId = @DirectoryEntryId

	INSERT INTO AccessControlList(DirectoryEntryId, [Role], [Permission])
	SELECT @DirectoryEntryId, [Role], [Permission]
	FROM @AccessControlList
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TrgDirectoryEntryIdToName]'))
DROP TRIGGER [dbo].[TrgDirectoryEntryIdToName]
GO

CREATE TRIGGER [dbo].[TrgDirectoryEntryIdToName]
   ON [dbo].[DirectoryEntry]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON

	UPDATE DirectoryEntry
	SET Name = '#' + CAST(ins.Id AS nvarchar(max))
	FROM 
		DirectoryEntry de INNER JOIN 
		INSERTED ins ON ins.Id = de.Id AND ins.Name IS NULL
END

GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TrgUpdatedOnInsert]'))
DROP TRIGGER [dbo].[TrgUpdatedOnInsert]
GO

CREATE TRIGGER [dbo].[TrgUpdatedOnInsert]
   ON  [dbo].[DirectoryEntry]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON

	UPDATE DirectoryEntry
	SET UpdatedOn = GETUTCDATE()
	FROM 
		DirectoryEntry de INNER JOIN INSERTED ins ON ins.ParentId = de.Id
END

GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TrgUpdatedOnUpdate]'))
DROP TRIGGER [dbo].[TrgUpdatedOnUpdate]
GO

CREATE TRIGGER [dbo].[TrgUpdatedOnUpdate]
   ON  [dbo].[DirectoryEntry]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON

	UPDATE DirectoryEntry
	SET UpdatedOn = GETUTCDATE()
	FROM 
		DirectoryEntry de INNER JOIN INSERTED ins ON ins.Id = de.Id
END

GO
